﻿using Dapper;
using System;
using System.Linq;
using System.Text;
using System.Collections.Generic;
using Eagles.Base.DataBase;
using Eagles.Base.DataBase.Modle;
using Eagles.DomainService.Model.Sms;
using Eagles.DomainService.Model.User;
using Eagles.Interface.DataAccess.UserInfo;

namespace Ealges.DomianService.DataAccess.UserInfo
{
    public class UserDataAccess : IUserInfoAccess
    {
        private readonly IDbManager dbManager;

        public UserDataAccess(IDbManager dbManager)
        {
            this.dbManager = dbManager;
        }

        public int InsertToken(TbUserToken userToken)
        {
            return dbManager.Excuted(@"insert into eagles.tb_user_token (UserId,Token,CreateTime,ExpireTime,TokenType,OrgId,BranchId) 
value (@UserId,@Token,@CreateTime,@ExpireTime,@TokenType,@OrgId,@BranchId)", userToken);
        }

        public int UpdateToken(TbUserToken userToken)
        {
            return dbManager.Excuted(@"update eagles.tb_user_token set ExpireTime = @ExpireTime, IsOut = @IsOut where UserId = @UserId",
                new {ExpireTime = DateTime.Now.AddSeconds(-1), IsOut = "1", UserId = userToken.UserId});
        }

        public int CreateUser(TbUserInfo userInfo)
        {
            return dbManager.Excuted(@"insert into eagles.tb_user_info (Phone,Password,CreateTime,OrgId,BranchId,Name,Score,Status,IsLeader,IsCustomer)
value (@Phone,@Password,@CreateTime,@OrgId,@BranchId,@Name,@Score,@Status,@IsLeader,@IsCustomer)", userInfo);
        }

        public int EditUser(TbUserInfo userInfo)
        {
            return dbManager.Excuted(@"update eagles.tb_user_info set Name=@Name,Marital=@Marital,Sex=@Sex,Birthday=@Birthday,Address=@Address,Origin=@Origin,OriginAddress=@OriginAddress,Ethnic=@Ethnic,
Dept=@Dept,Company=@Company,PhotoUrl=@PhotoUrl where UserId = @UserId", userInfo);
        }

        public int EditUserPwd(TbUserInfo userInfo)
        {
            return dbManager.Excuted(@"update eagles.tb_user_info set Password=@Password where Phone = @Phone", userInfo);
        }

        public int EditUserCity(TbUserInfo userInfo)
        {
            return dbManager.Excuted(@"update eagles.tb_user_info set LoginCity=@LoginCity where Phone = @Phone", userInfo);
        }

        public int EditUserCollectLog(string type, TbUserCollectLog tbUserCollectLog)
        {
            if (type == "0")
            {
                var sql = @"insert into eagles.tb_user_collect_log (OrgId, BranchId, UserId, NewsId, NewsType, NewsTitle, NewsUrl, NewsImage, CreateDate, Status, CollectType,Author)
VALUES(@OrgId, @BranchId, @UserId, @NewsId, @NewsType, @NewsTitle, @NewsUrl, @NewsImage, @CreateDate, @Status, @CollectType,@Author)";
                return dbManager.Excuted(sql, tbUserCollectLog);
            }
            else
            {
                return dbManager.Excuted(@"update eagles.tb_user_collect_log set Status = @Status where CollectType = @CollectType and NewsId = @NewsId and UserId = @UserId ", tbUserCollectLog);
            }
        }

        public int EditUserNoticeIsRead(int newsId)
        {
            return dbManager.Excuted(@"update eagles.tb_user_notice set IsRead = @IsRead, ReadTime = @ReadTime where NewsId = @NewsId", new {IsRead = 0, ReadTime = DateTime.Now, NewsId = newsId});
        }

        public TbUserInfo GetUserInfo(int userId)
        {
            var userInfo = dbManager.Query<TbUserInfo>(@"select OrgId,(Select OrgName from eagles.tb_org_info b where a.OrgId = b.OrgId) as OrgName,
BranchId,(Select BranchName from eagles.tb_branch c where a.BranchId = c.BranchId) as BranchName,UserId,Password,Name,Sex,Marital,Ethnic,Birthday,Origin,
OriginAddress,Phone,IdNumber,Education,School,Provice,City,District,Address,Company,Dept,Title,PreMemberTime,MemberTime,MemberType,Status,MemberStatus,
PhotoUrl,NickPhotoUrl,CreateTime,EditTime,OperId,IsCustomer,score,LoginErrorCount,FeeExpireDate,LoginCity FROM eagles.tb_user_info a where UserId=@UserId", new { UserId = userId });
            if (userInfo != null && userInfo.Any())
                return userInfo.FirstOrDefault();
            return null;
        }

        public TbUserInfo GetLogin(int orgId, string phone)
        {
            var userInfo = dbManager.Query<TbUserInfo>("select OrgId,BranchId,UserId,Password,IsCustomer,LoginCity from eagles.tb_user_info where Phone = @Phone and Status = 0 ", new { Phone = phone });
            if (userInfo != null && userInfo.Any())
                return userInfo.FirstOrDefault();
            return null;
        }

        public TbUserInfo GetPhoneExists(string phone)
        {
            var userInfo = dbManager.Query<TbUserInfo>("select UserId,Password,IsCustomer,OrgId from eagles.tb_user_info where Phone = @Phone ", new { Phone = phone });
            if (userInfo != null && userInfo.Any())
                return userInfo.FirstOrDefault();
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="relationshipType">ture 为查询上级信息  false 为查询下级信息</param>
        /// <param name="myUserId">自己的userId</param>
        /// <returns></returns>
        public List<TbUserRelationship> GetRelationship(int userId, bool relationshipType)
        {
            var sql = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            if (relationshipType)
            {
                //查询上级
                dynamicParams.Add("UserId", userId);
                sql.Append(@"select a.UserId,a.SubUserId,b.Name,b.PhotoUrl,(select Count(*) from eagles.tb_task c where a.UserId = c.FromUser) TaskCount 
from eagles.tb_user_relationship a inner join eagles.tb_user_info b on a.UserId = b.UserId where SubUserId = @UserId ");
            }
            else
            {
                //查询下级
                dynamicParams.Add("SubUserId", userId);
                sql.Append(@"select a.UserId,a.UserId,'我的'as Name,a.PhotoUrl,(select Count(*) from eagles.tb_task b where a.UserId = b.FromUser) TaskCount 
from eagles.tb_user_info a where UserId = @SubUserId
union 
select a.UserId,a.SubUserId,b.Name,b.PhotoUrl,(select Count(*) from eagles.tb_task c where a.SubUserId = c.FromUser) TaskCount 
from eagles.tb_user_relationship a inner join eagles.tb_user_info b on a.SubUserId = b.UserId where a.UserId = @SubUserId ");
            }

            return dbManager.Query<TbUserRelationship>(sql.ToString(), dynamicParams);
        }

        public List<TbUserInfo> GetUserInfo(List<int> userIdList)
        {
            var sql = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            sql.Append(@"select OrgId,BranchId,UserId,Password,Name,Sex,Ethinc,Birthday,Origin,OriginAddress,Phone,IdNumber,Eduction,School,Provice,City,District,Address,Company,Dept,
Title,PreMemberTime,MemberTime,MemberType,Status,MemberStatus,PhotoUrl,NickPhotoUrl,CreateTime,EditTime,OperId,IsCustomer,Score,IsLeader from eagles.tb_user_info where UserId in @UserId ");
            dynamicParams.Add("UserId", userIdList.ToArray());
            return dbManager.Query<TbUserInfo>(sql.ToString(), dynamicParams);
        }

        public List<TbUserNotice> GetUserNotice(int userId, int appId, int pageIndex = 1, int pageSize = 10)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            int pageIndexParameter = (pageIndex - 1) * pageSize;
            parameter.Append(" and OrgId = @OrgId ");
            dynamicParams.Add("OrgId", appId);
            parameter.Append(" and UserId = @UserId ");
            dynamicParams.Add("UserId", userId);
            parameter.Append(" or UserId = @UserId1 ");
            dynamicParams.Add("UserId1", 0);
            dynamicParams.Add("PageIndex", pageIndexParameter);
            dynamicParams.Add("PageSize", pageSize);
            sql.AppendFormat(@"select OrgId,NewsId,NewsType,Title,UserId,Content,IsRead,FromUser,CreateTime,TargetUrl from eagles.tb_user_notice 
where 1=1 {0} order by IsRead DESC ,CreateTime desc limit @PageIndex, @PageSize ", parameter);
            return dbManager.Query<TbUserNotice>(sql.ToString(), dynamicParams);
        }

        public TbValidCode GetValidCode(TbValidCode validCode)
        {
            return dbManager.QuerySingle<TbValidCode>("select Phone,ExpireTime from eagles.tb_validcode where Phone = @Phone and ValidCode = @ValidCode and Seq = @Seq ", validCode);
        }

        public int InsertSmsCode(TbValidCode validateCode)
        {
            var sql = @"insert into eagles.tb_validcode (OrgId,Phone,ValidCode,Seq,CreateTime,ExpireTime)
VALUES(@OrgId,@Phone,@ValidCode,@Seq,@CreateTime,@ExpireTime)";
            return dbManager.Excuted(sql, validateCode);
        }

        public List<TbUserInfo> GetBranchUser(int appId, int branchId, string name)
        {
            var sql = new StringBuilder(@"select a.OrgId,a.BranchId,a.UserId,a.Name,a.PhotoUrl,a.Password,a.IsCustomer,b.BranchName,c.OrgName from tb_user_info a
inner join tb_branch b on a.BranchId = b.BranchId inner join tb_org_info c on a.OrgId = c.OrgId where a.IsCustomer = 1 and a.Status = 0 and a.MemberStatus = 0 and a.MemberType = 0 ");
            var dynamicParams = new DynamicParameters();
            if (!string.IsNullOrEmpty(name))
            {
                sql.Append("and a.Name = @Name ");
                dynamicParams.Add("Name", name);
            }
            if (branchId > 0)
            {
                sql.Append("and a.BranchId = @BranchId ");
                dynamicParams.Add("BranchId", branchId);
            }
            else
            {
                sql.Append("and a.OrgId = @OrgId ");
                dynamicParams.Add("OrgId", appId);
            }
            return dbManager.Query<TbUserInfo>(sql.ToString(), dynamicParams);
        }

        public int GetActivityCount(int userId, int branchId)
        {
            return dbManager.ExecuteScalar<int>(@"select count(*) from eagles.tb_activity where Status = 0 and ActivityId 
not in (select ActivityId from eagles.tb_user_activity where UserId = @UserId) and BranchId = @BranchId ", new { UserId = userId, BranchId = branchId });
        }

        public int GetTaskCount(int userId)
        {
            return dbManager.ExecuteScalar<int>(@"select count(*) from eagles.tb_task a join eagles.tb_user_task b on a.TaskId = b.TaskId where a.Status <> -9 and b.UserId = @UserId ", new { UserId = userId });
        }

        public List<TbUserInfo> GetOrgUser(int orgId)
        {
            var sql = new StringBuilder();
            sql.Append(@"select a.OrgId,a.UserId,a.Name,a.PhotoUrl,b.BranchName,(select Count(*) from eagles.tb_task d where a.UserId = d.FromUser) TaskCount from eagles.tb_user_info a
inner join eagles.tb_branch b on a.BranchId = b.BranchId 
where exists (
select c.SubUserId from tb_user_relationship c 
where c.SubUserId = a.UserId
)
and a.IsCustomer = 1 and a.Status = 0 and a.MemberStatus = 0 
and a.orgId = @OrgId ");
            var userInfo = dbManager.Query<TbUserInfo>(sql.ToString(), new { OrgId = orgId });
            return userInfo;
        }

        public List<TbUserCollectLog> GeUserCollectLog(string collectType, int appId, int branchId, int userId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            parameter.Append("and CollectType = @CollectType ");
            dynamicParams.Add("CollectType", collectType);
            parameter.Append("and UserId = @UserId ");
            dynamicParams.Add("UserId", userId);
            parameter.Append("and BranchId = @BranchId ");
            dynamicParams.Add("BranchId", branchId);
            parameter.Append("and OrgId = @OrgId ");
            dynamicParams.Add("OrgId", appId);
            parameter.Append("and Status = @Status ");
            dynamicParams.Add("Status", "0");
            sql.AppendFormat(@"select OrgId, BranchId, TransId, UserId, NewsId, NewsType, NewsTitle, NewsUrl, NewsImage, CreateDate, Status, CollectType, Author from tb_user_collect_log where 1=1 {0}", parameter);
            return dbManager.Query<TbUserCollectLog>(sql.ToString(), dynamicParams);
        }

        public TbUserCollectLog GetUserCollectLog(string collectType, int newsId, int userId)
        {
            var sql = new StringBuilder();
            var parameter = new StringBuilder();
            var dynamicParams = new DynamicParameters();
            parameter.Append("and CollectType = @CollectType ");
            dynamicParams.Add("CollectType", collectType);
            parameter.Append("and NewsId = @NewsId ");
            dynamicParams.Add("NewsId", newsId);
            parameter.Append("and UserId = @UserId ");
            dynamicParams.Add("UserId", userId);
            sql.AppendFormat(@"select NewsId,Status from tb_user_collect_log where 1=1 {0}", parameter);
            var result = dbManager.Query<TbUserCollectLog>(sql.ToString(), dynamicParams);
            if (result != null && result.Any())
                return result.FirstOrDefault();
            return null;
        }
    }
}